﻿'************************************************************************************'
'* Copyright (c) 2009, Patrick Driscoll All rights reserved.                        *'
'*                                                                                  *'
'* Redistribution and use in source and binary forms, with or without               *'
'* modification, are permitted provided that the following conditions are met:      *'
'*                                                                                  *'
'* Redistributions of source code must retain the above copyright notice, this      *'
'* list of conditions and the following disclaimer. Redistributions in binary       *'
'* form must reproduce the above copyright notice, this list of conditions and      *'
'* the following disclaimer in the documentation and/or other materials             *'
'* provided with the distribution. Neither the name of the DriscollArt Software nor *'
'* the names of its contributors may be used to endorse or promote products         *'
'* derived from this software without specific prior written permission.            *'
'*                                                                                  *'
'* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"      *'
'* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE        *'
'* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE       *'
'* ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR      *'
'* ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL           *'
'* DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR       *'
'* SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER       *'
'* CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT               *'
'* LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY        *'
'* OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH      *'
'* DAMAGE.                                                                          *'
'************************************************************************************'

Imports System.Data.SqlClient

''' <summary>
''' SQL Information Class
''' </summary>
''' <remarks>Returns lists of tables, fields and stored procedures</remarks>
''' 
Public Class SQLTablesInfo
    Private _ConnectionString As String

    Private TablesQuery As String = "select distinct table_name from information_schema.columns order by table_name"
    Private FieldsQuery As String = "select * from information_schema.columns where table_name = '[TABLENAME]' order by table_name"
    Private KeysQuery As String = "EXEC sp_pkeys '[TABLENAME]'" '"SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name = '[TABLENAME]'"

    ''' <summary>
    ''' Object instantiation
    ''' </summary>
    ''' <param name="ConnectionString">Any valid SQL connection string (must specify a database)</param>
    ''' <remarks></remarks>
    Public Sub New(ByVal ConnectionString As String)
        _ConnectionString = ConnectionString
    End Sub

    ''' <summary>
    ''' The connection string property (needs to specify database)
    ''' </summary>
    ''' <value>Any valid SQL connection string (must specify a database)</value>
    ''' <returns>Any valid SQL connection string (must specify a database)</returns>
    ''' <remarks></remarks>
    Public Property ConnectionString() As String
        Get
            Return _ConnectionString
        End Get
        Set(ByVal value As String)
            _ConnectionString = value
        End Set
    End Property

    ''' <summary>
    ''' this property is a list of all tables from the database
    ''' </summary>
    ''' <value></value>
    ''' <returns>Datatable of all tables</returns>
    ''' <remarks></remarks>
    Public ReadOnly Property Tables() As DataTable
        Get
            Try
                Dim _Tables As New DataTable("Tables")
                Dim Adapter_Tables As New SqlDataAdapter(TablesQuery, _ConnectionString)
                Adapter_Tables.Fill(_Tables)
                Return _Tables
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Get
    End Property

    ''' <summary>
    ''' this property is a list of all tables from the database
    ''' </summary>
    ''' <value></value>
    ''' <returns>Datatable of all tables</returns>
    ''' <remarks></remarks>
    Public ReadOnly Property PrimaryKeys(ByVal TableName As String) As DataTable
        Get
            Try
                Dim _TableInfo As New DataSet ' DataTable("Tables")
                Dim Adapter_Tables As New SqlDataAdapter(KeysQuery.Replace("[TABLENAME]", TableName), _ConnectionString)

                Adapter_Tables.Fill(_TableInfo)
                Return _TableInfo.Tables(0)
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Get
    End Property

    ''' <summary>
    ''' this property returns a list of all fields for a particular table
    ''' </summary>
    ''' <param name="TableName">SQL table name</param>
    ''' <value></value>
    ''' <returns>data table of all fields for your table</returns>
    ''' <remarks></remarks>
    Public ReadOnly Property Fields(ByVal TableName As String) As DataTable
        Get
            Try
                Dim _Fields As New DataTable("Fields")
                Dim Adapter_Fields As New SqlDataAdapter(FieldsQuery.Replace("[TABLENAME]", TableName), _ConnectionString)
                Adapter_Fields.Fill(_Fields)
                Return _Fields
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Get
    End Property
End Class
